﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Shuba.Model;
using System.Data;

namespace Shuba.Data
{
    public class branch_rollout_mobile_version
    {
        public branch_rollout_mobile_version() { }

        public List<string> getVersion()
        {
            List<string> list = new List<string>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT VERSION FROM TBL_MS_MOBILE_FILE_VERSION WHERE CHECKSUM > 0 GROUP BY VERSION ");

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(dr.GetString(0));
                }
                dr.Close();
            }
            return list;
        }      

        public List<branch_rollout_versionInfo> getBranchRollout()
        {
            List<branch_rollout_versionInfo> list = new List<branch_rollout_versionInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT A.BRANCH_ID, B.BRANCH_NAME, A.IMPLEMENTATION_DATE
                           FROM TBL_BRANCH_ROLLOUT_MOBILE_VERSION A
                           INNER JOIN TBL_MS_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetDateTime(2)));
                }
                dr.Close();
            }
            return list;
        }

        public List<branch_rollout_versionInfo> getBranchRollout(string region_id, string cluster_id, string version)
        {
            List<branch_rollout_versionInfo> list = new List<branch_rollout_versionInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT A.BRANCH_ID, B.BRANCH_NAME, A.IMPLEMENTATION_DATE 
                           FROM TBL_BRANCH_ROLLOUT_MOBILE_VERSION A
                            INNER JOIN TBL_MS_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
                            INNER JOIN TBL_MS_CLUSTER_BRANCH CB ON B.BRANCH_ID = CB.BRANCH_ID
                            INNER JOIN TBL_MS_REGION_CLUSTER RC ON CB.CLUSTER_ID = RC.CLUSTER_ID
                           WHERE 
                            (RC.REGIONAL_ID LIKE '' + @REGIONAL_ID + '') AND
                            (CB.CLUSTER_ID LIKE '' + @CLUSTER_ID + '')  AND 
                            A.VERSION = @VERSION";

            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, "%" + region_id + "%");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, "%" + cluster_id + "%");
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetDateTime(2)));
                }
                dr.Close();
            }
            return list;
        }

        public List<branch_rollout_versionInfo> GetList(string value, string cluster_id, string regoinal_id)
        {
            List<branch_rollout_versionInfo> list = new List<branch_rollout_versionInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_Search_View_Text");
            db.AddInParameter(cmd, "@PARAM", DbType.String, value);//"%" + value + "%");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, cluster_id);
            db.AddInParameter(cmd, "@REGION_ID", DbType.String, regoinal_id);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4)));
                }
                dr.Close();
            }
            return list;
        }

        public List<branch_rollout_versionInfo> GetListApproval(string update_by)
        {
            List<branch_rollout_versionInfo> list = new List<branch_rollout_versionInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCLS_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_LIST_APPROVE");
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, update_by);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetDateTime(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }

        public branch_rollout_versionInfo Get(string id, string version)
        {
            branch_rollout_versionInfo bi = new branch_rollout_versionInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_Search_BY_ID");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetDateTime(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8));
                }
                dr.Close();
            }
            return bi;
        }

        public List<branch_rollout_versionInfo> GetListAll()
        {
            List<branch_rollout_versionInfo> list = new List<branch_rollout_versionInfo>();
            Database db = DBConnection.CreateDatabase();
            string sql = @"SELECT BRANCH_ID, VERSION, IMPLEMENTATION_DATE, 
                           IS_DELETED, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE 
                           FROM TBL_BRANCH_ROLLOUT_MOBILE_VERSION ORDER BY BRANCH_ID";

            DbCommand cmd = db.GetSqlStringCommand(sql);

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetDateTime(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8)));
                }
                dr.Close();
            }
            return list;
        }


        public branch_rollout_versionInfo GetApprovalByID(string id, string version)
        {
            branch_rollout_versionInfo bi = new branch_rollout_versionInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_GET_APPROVE_BY_ID");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, id);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new branch_rollout_versionInfo(dr.GetString(0), dr.GetString(1), dr.GetDateTime(2), dr.GetString(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6), dr.GetDateTime(7), dr.GetString(8));
                }
                dr.Close();
            }
            return bi;
        }

        public Int32[] deleteAll()
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_DELETE_ALL");

            db.AddOutParameter(cmd, "@ROWCOUNT_I", DbType.Int32, 50);
            db.AddOutParameter(cmd, "@ROWCOUNT_U", DbType.Int32, 50);
            cmd.CommandTimeout = 1800;
            db.ExecuteNonQuery(cmd);
            int insertCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_I"));
            int updateCount = Convert.ToInt32(db.GetParameterValue(cmd, "@ROWCOUNT_U"));
            Int32[] countArr = new Int32[2] { insertCount, updateCount };
            return countArr;
        }

        public int Save(branch_rollout_versionInfo branch_rollout_version)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_SAVE");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branch_rollout_version.BranchID);
            db.AddInParameter(cmd, "@VERSION", DbType.String, branch_rollout_version.version);
            db.AddInParameter(cmd, "@IMPLEMENTATION_DATE", DbType.DateTime, branch_rollout_version.implementation_date);
            db.AddInParameter(cmd, "@IS_DELETED", DbType.String, branch_rollout_version.is_delete);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, branch_rollout_version.entry_by);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, branch_rollout_version.entry_date);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, branch_rollout_version.update_by);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, branch_rollout_version.update_date);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, branch_rollout_version.ActType);

            return db.ExecuteNonQuery(cmd);
        }

        public void Approve(string branch_id, string version, string ActType)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_APPROVAL");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branch_id);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);
            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string branch_id, string version)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("spCls_TBL_BRANCH_ROLLOUT_MOBILE_VERSION_REJECT_BY_ID");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branch_id);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);
            db.ExecuteNonQuery(cmd);
        }

        public bool IsPending(string branch_id, string version)
        {
            bool isExist = false;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT TOP 1 * FROM TBL_BRANCH_ROLLOUT_MOBILE_VERSION_TMP WHERE BRANCH_ID = @BRANCH_ID AND VERSION = @VERSION");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branch_id);
            db.AddInParameter(cmd, "@VERSION", DbType.String, version);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    isExist = true;
                }
                dr.Close();
            }
            return isExist;
        }
    }
}